import pymysql
from pymysql.cursors import DictCursor
qhjy_db=pymysql.connect(host='192.168.1.100',user='root',password='root',database='qhjy',charset="utf8")
def hhy():
    qhjy=qhjy_db.cursor(DictCursor)
    sql = '''SELECT d.up_people_count as up_people ,e.flat_people_count flat_people , f.down_people_count as down_people , a.price as up_price , b.price as flat_price , c.price as down_price FROM
     (
       SELECT SUM(qhjy.qh_betting.price) as price , COUNT(qhjy.qh_betting.id) as people_count FROM qhjy.qh_betting
         INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
       WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 1 and qh_betting.is_true = 1
     ) a,
     (
       SELECT SUM(qhjy.qh_betting.price) as price , COUNT(qhjy.qh_betting.id) as people_count FROM qhjy.qh_betting
         INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
       WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 2 and qh_betting.is_true = 1
     ) b,
     (
       SELECT SUM(qhjy.qh_betting.price)  as price , COUNT(qhjy.qh_betting.id) as people_count FROM qhjy.qh_betting
         INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
       WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 3 and qh_betting.is_true = 1
     ) c,
     (
       SELECT count(_ciunt) as up_people_count  FROM (SELECT count(*) as _ciunt FROM qhjy.qh_betting
         INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
       WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 1 and qh_betting.is_true = 1
       GROUP BY qhjy.qh_betting.user_id) a
     )d,
     (
       SELECT count(_ciunt) as flat_people_count FROM (SELECT count(*) as _ciunt FROM qhjy.qh_betting
         INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
       WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 2 and qh_betting.is_true = 1
       GROUP BY qhjy.qh_betting.user_id) a
     )e,
     (
       SELECT count(_ciunt) as down_people_count  FROM (SELECT count(*) as _ciunt FROM qhjy.qh_betting
         INNER JOIN qhjy.qh_nper ON qhjy.qh_nper.id = qhjy.qh_betting.nper_id
       WHERE qhjy.qh_nper.is_current = 3 AND qhjy.qh_nper.coin_id = 3 AND qhjy.qh_betting.type = 3 and qh_betting.is_true = 1
       GROUP BY qhjy.qh_betting.user_id) a
     )f ;'''
    qhjy.execute(sql)
    res=qhjy.fetchone()
    if not res['up_price']:
        res['up_price']=0
    if not res['flat_price']:
        res['flat_price'] = 0
    if not res['down_price']:
        res['down_price']=0
    all=res['up_price']+res["flat_price"]+res["down_price"]
    if not all:
        up_rate=down_rate=flat_rate=0
    else:
        up_rate=res['up_price']/all
        flat_rate=  res['flat_price']/all
        down_rate= res['down_price']/all
    qhjy.execute('select id,up_rate,down_rate,flat_rate from qh_nper where coin_id=3 and is_current = 1')
    nper_ids = qhjy.fetchall()

    if nper_ids:
        nper_id = nper_ids['id']
    print(up_rate,down_rate,flat_rate)
    sql=f'update qh_nper set  up_rate={up_rate},flat_rate={flat_rate},down_rate={down_rate} where id={nper_id}'
    try:
        qhjy.execute(sql)
        qhjy_db.commit()
    except:
        qhjy_db.rollback()
hhy()
